﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace AuriaCompte.Lib.Dao
{
    public class DaoBase
    {

        /// <summary>
        /// Ensemble de paramétre MySQL des requêtes
        /// </summary>
        protected List<SQLiteParameter> lParam = new List<SQLiteParameter>();
        /// <summary>
        /// Nom de la table associée à l'élément en cours
        /// </summary>
        protected string TableName = string.Empty;
        /// <summary>
        /// Nom de la vue associée à l'élément en cours
        /// </summary>
        protected string ViewName = string.Empty;
        /// <summary>
        /// Nom de la clé primaire de la table
        /// </summary>
        protected string PrimaryKeyName = string.Empty;
        protected List<String> ListColonne = new List<String>();



        /// <summary>
        /// Etabli le SELECT complet (juste avant le FROM) du composant en cours
        /// </summary>
        protected string DebutSelect
        {
            get
            {
                string retour = "SELECT ";

                // Initialisation du compteur
                int i = 1;
                // On parcourt toute la liste
                foreach (string s in this.ListColonne)
                {
                    // On construit la liste des colonnes
                    retour += s;
                    // Si dernière colonne, on ne précise pas la virgule
                    if (i < this.ListColonne.Count)
                        retour += ",";
                    i++;
                }
                retour += " FROM ";
                return retour;
            }
        }
        public long LastInsertId;

        /// <summary>
        /// Supprimme l'enregistrement de la table du DAO 
        /// </summary>
        /// <param name="OrionId">Identifiant unique de la table</param>
        public void Delete(Int32 PK)
        {
            // Préparation de la requête
            String req = "DELETE FROM " + this.TableName + " WHERE " + this.PrimaryKeyName + "=@PK";
            // On nettoie les paramétres
            lParam.Clear();
            // On ajoute le paramétre à la liste
            lParam.Add(new SQLiteParameter("@PK", PK));
            // On exécute la requête avec la liste des paramétres
            this.ExecuteNonQuery(req, lParam);
        }

        /// <summary>
        /// Renvoie le contenu complet de la table dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectAllDS()
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            if (this.TableName != string.Empty)
            {
                String req = this.DebutSelect
                    + this.ViewName;

                retour = this.ExecuteQueryDataSet(req);
            }
            return retour;
        }

        /// <summary>
        /// Renvoie le contenu complet de la Vue dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectAllDSView()
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            if (this.TableName != string.Empty)
            {
                String req = "SELECT * FROM "
                    + this.ViewName;

                retour = this.ExecuteQueryDataSet(req);
            }
            return retour;
        }

        /// <summary>
        /// Renvoie le contenu complet de la Vue dans un DataSet
        /// </summary>
        /// <returns>DataSet : Vu compplète de la table</returns>
        public DataSet SelectFromRequete(string req)
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            retour = this.ExecuteQueryDataSet(req);
            return retour;
        }

        public Int32 GetLastInsertId()
        {
            SQLiteCommand IDCmd = new SQLiteCommand("Select last_insert_rowid();", Tools.GetConnexion());
            IDCmd.ExecuteNonQuery();
            return Convert.ToInt32(IDCmd.ExecuteScalar());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        protected Int32 ExecuteNonQuery(String req, List<SQLiteParameter> lParam)
        {
            Int32 retour = 0;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(req, Tools.GetConnexion());
                foreach (SQLiteParameter param in lParam)
                    cmd.Parameters.Add(param);
                cmd.Prepare();
                retour = cmd.ExecuteNonQuery();
                if (req.StartsWith("INSERT"))
                    this.LastInsertId = this.GetLastInsertId();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected Int32 ExecuteNonQuery(String req)
        {
            Int32 retour = 0;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(req, Tools.GetConnexion());
                retour = cmd.ExecuteNonQuery();
                if (req.Contains("INSERT"))
                    this.LastInsertId = this.GetLastInsertId();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteQueryReader(String req)
        {
            SQLiteDataReader retour = null;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(req, Tools.GetConnexion());
                retour = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <param name="lParam"></param>
        /// <returns></returns>
        public SQLiteDataReader ExecuteQueryReader(String req, List<SQLiteParameter> lParam)
        {
            SQLiteDataReader retour = null;
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(req, Tools.GetConnexion());
                foreach (SQLiteParameter param in lParam)
                    cmd.Parameters.Add(param);
                cmd.Prepare();
                retour = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected DataSet ExecuteQueryDataSet(String req)
        {
            DataSet retour = new DataSet();
            SQLiteDataAdapter mda;
            try
            {
                mda = new SQLiteDataAdapter(req, Tools.GetConnexion());
                SQLiteCommandBuilder cb = new SQLiteCommandBuilder(mda);
                mda.Fill(retour);
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        protected DataSet ExecuteQueryDataSet(String req, List<SQLiteParameter> lParam)
        {
            DataSet retour = new DataSet();
            SQLiteDataAdapter mda;
            try
            {
                mda = new SQLiteDataAdapter(req, Tools.GetConnexion());
                foreach (SQLiteParameter param in lParam)
                    mda.SelectCommand.Parameters.Add(param);
                mda.SelectCommand.Prepare();
                mda.Fill(retour);
            }
            catch (Exception ex)
            {
                if (ex.InnerException != null)
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.InnerException.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
                else
                    ComponentFactory.Krypton.Toolkit.KryptonMessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK);
            }
            return retour;
        }
    }
}
